﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Windows.Forms;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using System.IO;
using System.Data;
using SDD;
using NPOI.HSSF.UserModel;

namespace SDD
{
    class TrdePerMin
    {
        //http://b2b.cnyes.com/twstock/eye/smart_tech.asp?date=20100210&typekind=TECH&market=TSE&rptkind=VVPerMin&id=voice&tel=12508

        public string url1 = string.Format("http://www.twse.com.tw/ch/trading/exchange/MI_5MINS/genpage/Report{0}{1}/A125{0}{1}{2}.php?chk_date={3}/{1}/{2}",
                                                                    Program.DataDay.Substring(0, 4),
                                                                    Program.DataDay.Substring(4, 2),
                                                                    Program.DataDay.Substring(6, 2),
                                                                    (Convert.ToInt32(Program.DataDay.Substring(0, 4)) - 1911));
        public string url2 = string.Format("http://b2b.cnyes.com/twstock/eye/smart_tech.asp?date={0}&typekind=TECH&market=TSE&rptkind=VVPerMin&id=voice&tel=12508", Program.DataDay);
        private string FullFileName = Program.FileDir + "每分鐘成交統計.xls";

        public string dataDay = Program.DataDay;
        public string msg = "";


        public void AggregateData()
        {
            try
            {
                // 證交所
                analysisHTML(url1);
                // 元富股市大亨
                //analysisHTML2(url2);
            }
            catch
            {
                Console.WriteLine("每分鐘成交統計 資料擷取失敗!!");
            }

        }
        /// <summary>
        /// 解析HTML new
        /// </summary>
        /// <param name="url"></param>
        /// <param name="ds"></param>
        public void analysisHTML(string url)
        {
            string context = new cHttp().dopost(url);
            WebBrowser wbr = new WebBrowser();
            wbr.Navigate("");

            wbr.ScriptErrorsSuppressed = true;
            wbr.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            HtmlElementCollection tb = wbr.Document.GetElementsByTagName("TABLE");
            HtmlElementCollection tr = tb[7].GetElementsByTagName("TR");
            //string[] dataday = tb[12].InnerText.Substring(tb[12].InnerText.IndexOf("：") + 1).Split('/');
            //string text = "";

            /////////////////////
            //建立工作表
            NPOIComFunction xls = new NPOIComFunction(FullFileName);
            if (xls.Workbook.GetSheetIndex(dataDay) > -1)
            {
                Console.WriteLine("每分鐘成交統計:  " + dataDay + " 資料已存在!!!");
                return;
            }

            HSSFSheet sheet = xls.Workbook.CreateSheet(dataDay);

            int r = 2;     // 要寫入excel的row順序
            int rno = 0; // tr的順序

            GetTemplete(sheet);
            foreach (HtmlElement row in tr)
            {
                rno++;
                if (rno <= 2)
                    continue;

                int c = 0;     // 要寫入excel的cell順序
                int cnt = 0; //td 的順序
                foreach (HtmlElement Children in row.Children)
                {
                    try
                    {
                        cnt++;
                        if (cnt == 8)
                        {
                            c++;
                            //continue;
                        }
                        //if (c == 8)
                        //    c++;
                        

                        if (sheet.GetRow(r).GetCell(c) == null)
                            sheet.CreateRow(r).CreateCell(c).SetCellValue(Children.InnerText);
                        else
                            sheet.GetRow(r).GetCell(c).SetCellValue(Children.InnerText);
                        c++;
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine("*****************************");
                        Debug.WriteLine(ex.Message);
                        //throw;
                    }
                   
                    
                }

                r++;
            }
            // 寫入實體檔案
            //sheet.
            xls.WriteToFile();
            
        }

        /// <summary>
        /// 解析HTML 元富股市大亨
        /// </summary>
        /// <param name="url"></param>
        /// <param name="ds"></param>
        public void analysisHTML2(string url)
        {
            string context = new cHttp().dopost(url);
            WebBrowser wbr = new WebBrowser();
            wbr.Navigate("");

            wbr.ScriptErrorsSuppressed = true;
            wbr.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            HtmlElementCollection tb = wbr.Document.GetElementsByTagName("TABLE");
            HtmlElementCollection tr = tb[7].GetElementsByTagName("TR");

            /////////////////////
            //建立工作表
            NPOIComFunction xls = new NPOIComFunction(FullFileName);
            if (xls.Workbook.GetSheetIndex(dataDay) > -1)
            {
                Console.WriteLine("每分鐘成交統計:  " + dataDay + " 資料已存在!!!");
                return;
            }

            HSSFSheet sheet = xls.Workbook.CreateSheet(dataDay);

            int r = 2;     // 要寫入excel的row順序
            int rno = 0; // tr的順序

            GetTemplete2(sheet);
            foreach (HtmlElement row in tr)
            {
                rno++;
                if (rno <= 2)
                    continue;

                int c = 0;     // 要寫入excel的cell順序
                int cnt = 0; //td 的順序
                foreach (HtmlElement Children in row.Children)
                {
                    cnt++;
                    if (cnt >= 3 && cnt <= 8)
                        continue;
                    if (sheet.GetRow(r).GetCell(c) == null)
                        sheet.CreateRow(r).CreateCell(c).SetCellValue(Children.InnerText);
                    else
                        sheet.GetRow(r).GetCell(c).SetCellValue(Children.InnerText);
                    c++;
                }

                r++;
            }

            //讓目前的工作表選為預設
            xls.Workbook.ActiveSheetIndex = xls.Workbook.NumberOfSheets - 1;
            xls.Workbook.SetSelectedTab(xls.Workbook.NumberOfSheets - 1);
            sheet.IsSelected = true;

            // 寫入實體檔案
            xls.WriteToFile();


        }

        public void GetTemplete2(HSSFSheet newSheet)
        {
            FileStream file = new FileStream(@"template/sample.xls", FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            HSSFSheet oldSheet = hssfworkbook.GetSheet("江波");
            file.Close();

            for (int i = 0; i <= oldSheet.LastRowNum; i++)
            {
                for (int j = 0; j <= oldSheet.GetRow(i).LastCellNum; j++)
                {
                    try
                    {
                        if (i < 2)
                            newSheet.CreateRow(i).CreateCell(j).SetCellValue(oldSheet.GetRow(i).GetCell(j).ToString());
                    }
                    catch { }

                    try
                    {
                        if (j > 10 && i > 1)
                            newSheet.CreateRow(i).CreateCell(j).SetCellFormula(oldSheet.GetRow(i).GetCell(j).CellFormula);
                    }
                    catch { }
                }

            }
        }

        public void GetTemplete(HSSFSheet newSheet)
        {
            FileStream file = new FileStream(@"template/sample.xls", FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            HSSFSheet oldSheet = hssfworkbook.GetSheet("江波");
            file.Close();

            for (int i = 0; i <= oldSheet.LastRowNum; i++)
            {
                for (int j = 0; j <= oldSheet.GetRow(i).LastCellNum; j++)
                {
                    try
                    {
                        if (i < 2)
                            newSheet.CreateRow(i).CreateCell(j).SetCellValue(oldSheet.GetRow(i).GetCell(j).ToString());
                    }
                    catch { }

                    try
                    {
                        if (j > 8 && i > 1)
                            newSheet.CreateRow(i).CreateCell(j).SetCellFormula(oldSheet.GetRow(i).GetCell(j).CellFormula);
                        if (j == 7 && i > 1)
                            newSheet.CreateRow(i).CreateCell(j).SetCellFormula(oldSheet.GetRow(i).GetCell(j).CellFormula);

                    }
                    catch { }
                }

            }
        }
        public HSSFWorkbook GetTemplete()
        {
            FileStream file = new FileStream(@"template/sample.xls", FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            HSSFSheet sheet = hssfworkbook.GetSheet("江波");
            file.Close();
            return hssfworkbook;

        }

    }
}
